import requests
import pandas as pd
import numpy as np
from io import StringIO
import matplotlib.pyplot as plt
import seaborn as sns
import folium
from IPython.display import display
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error
from sklearn.metrics import mean_absolute_error
from statsmodels.tsa.arima.model import ARIMA
from statsmodels.tsa.statespace.sarimax import SARIMAX
from sklearn.cluster import KMeans
from matplotlib.cm import get_cmap
import matplotlib.colors as mcolors
from sklearn.preprocessing import MinMaxScaler

# Collect_data function
def collect_data(dataset_id):
    base_url = 'https://data.melbourne.vic.gov.au/api/explore/v2.1/catalog/datasets/'
    dataset_id = dataset_id
    format = 'csv'


    url = f'{base_url}{dataset_id}/exports/{format}'
    params = {
        'select': '*',
        'limit': -1,
        'lang': 'en',
        'timezone': 'UTC',
    }

    response = requests.get(url, params=params)


    if response.status_code == 200:
        url_content = response.content.decode('utf-8')
        dataset = pd.read_csv(StringIO(url_content), delimiter=';')
        return dataset
    else:
        print(f'Request failed with status code {response.status_code}')

# Pedestrian counting data
dataset_id = 'pedestrian-counting-system-monthly-counts-per-hour'
data_1 = collect_data(dataset_id)
data_1.shape

(1850393, 9)

# Bus stop for Melbourne visitor shuttle data
dataset_id_2 = 'bus-stops-for-melbourne-visitor-shuttle'
data_2 = collect_data(dataset_id_2)
data_2.shape

(13, 4)

# Public artworks, fountains, and monuments data
dataset_id_3 = 'public-artworks-fountains-and-monuments'
data_3 = collect_data(dataset_id_3)
data_3.shape

(261, 14)

# Domestic visitor data from visit victoria
#data_4 = pd.read_csv('/content/sample_data/Domestic overnight visitors.csv')
data_4 = pd.read_csv('https://raw.githubusercontent.com/Chameleon-company/MOP-Code/taehwan/datascience/usecases/DEPENDENCIES/Domestic_overnight_visitors.csv')
data_4.shape

(41, 3)

# Drop unnecessary features
data_3_cleaned = data_3.drop(columns=['easting', 'northing','xorg','xsource','artist','art_date','respective_author', 'structure', 'alternate_name'])
data_3_cleaned.head()

# Na values check
data_1.isna().sum()
data_2.isna().sum()
data_3_cleaned.isna().sum()
data_4.isna().sum()

# To pandas datetime type
data_1_1 = data_1
data_1_1['sensing_date'] = pd.to_datetime(data_1['sensing_date'])
data_1_1['year_month'] = data_1_1['sensing_date'].dt.to_period('M').dt.end_time.dt.date
# Summing 'pedestriancount' by 'sensor_name', 'year_month', and 'location'
data_1_1 = data_1_1.groupby(['sensor_name', 'year_month', 'location']).agg({
    'pedestriancount': 'sum'
}).reset_index()
# Checking conversion
data_1_1.head()

# Data_1 (pedestrian data) filtering for regression analysis
data_1_2 = data_1[data_1['sensing_date'].dt.year.isin([2021, 2022, 2023, 2024])]
data_1_2.head()
# 'sorting 'sensing_date' by quarter
data_1_2['year_quarter'] = data_1_2['sensing_date'].dt.to_period('Q')
# Summing 'pedestriancount' by 'year_quarter'
data_1_2 = data_1_2.groupby(['year_quarter']).agg({
    'pedestriancount': 'sum'
}).reset_index()
# Checking conversion
data_1_2.head()

# Data_1 filtering for Time series
data_1_3 = data_1
data_1_3['year_day'] = data_1_3['sensing_date'].dt.to_period('D').dt.end_time.dt.date
# Summing 'pedestriancount' by 'sensor_name', 'year_month', and 'location'
data_1_3 = data_1_3.groupby(['sensor_name', 'year_day', 'location']).agg({
    'pedestriancount': 'sum'
}).reset_index()
data_1_3.head()

# Convert datatype of 'data_4'(visitor_count data)
# To pandast datetime type
data_4['DateTime'] = pd.to_datetime(data_4['DateTime'])
# Data filtering
data_4_filtered = data_4[data_4['DateTime'].dt.year.isin([2021, 2022, 2023, 2024])]
data_4_filtered['DateTime'] = data_4_filtered['DateTime'].dt.to_period('Q')
data_4_filtered['year_quarter'] = data_4_filtered['DateTime']
# Dropping unecessary column
data_4_filtered = data_4_filtered.drop('DateTime',axis=1)
data_4_filtered = data_4_filtered.drop('Regional VIC',axis=1)
data_4_filtered = data_4_filtered.rename(columns={'Melbourne': 'visitor_count'})
# Checking result
data_4_filtered.head()

<ipython-input-12-b16dda1176e8>:3: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.
  data_4['DateTime'] = pd.to_datetime(data_4['DateTime'])
<ipython-input-12-b16dda1176e8>:6: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  data_4_filtered['DateTime'] = data_4_filtered['DateTime'].dt.to_period('Q')
<ipython-input-12-b16dda1176e8>:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  data_4_filtered['year_quarter'] = data_4_filtered['DateTime']

# Create columns of 'latitude'and 'longitude' for folium mapping
# data_1_1
data_1_1[['latitude', 'longitude']] = data_1_1['location'].str.split(',', expand=True)
data_1_1['latitude'] = pd.to_numeric(data_1_1['latitude'], errors='coerce')
data_1_1['longitude'] = pd.to_numeric(data_1_1['longitude'], errors='coerce')
# data_1_3
data_1_3[['latitude', 'longitude']] = data_1_3['location'].str.split(',', expand=True)
data_1_3['latitude'] = pd.to_numeric(data_1_3['latitude'], errors='coerce')
data_1_3['longitude'] = pd.to_numeric(data_1_3['longitude'], errors='coerce')
#data_2
data_2[['latitude', 'longitude']] = data_2['co_ordinates'].str.split(',', expand=True)
data_2['latitude'] = pd.to_numeric(data_2['latitude'], errors='coerce')
data_2['longitude'] = pd.to_numeric(data_2['longitude'], errors='coerce')
#data_3_cleaned
data_3_cleaned[['latitude', 'longitude']] = data_3_cleaned['co_ordinates'].str.split(',', expand=True)
data_3_cleaned['latitude'] = pd.to_numeric(data_3_cleaned['latitude'], errors='coerce')
data_3_cleaned['longitude'] = pd.to_numeric(data_3_cleaned['longitude'], errors='coerce')
data_3_cleaned['latitude'] = data_3_cleaned['latitude'].astype(float)
data_3_cleaned['longitude'] = data_3_cleaned['longitude'].astype(float)
#Checking results
print(data_1_3.head())

  sensor_name    year_day                    location  pedestriancount  \
0   261Will_T  2022-11-17  -37.81295822, 144.95678789             3993   
1   261Will_T  2022-11-18  -37.81295822, 144.95678789            11594   
2   261Will_T  2022-11-19  -37.81295822, 144.95678789             3200   
3   261Will_T  2022-11-20  -37.81295822, 144.95678789             2855   
4   261Will_T  2022-11-21  -37.81295822, 144.95678789            11506   

    latitude   longitude  
0 -37.812958  144.956788  
1 -37.812958  144.956788  
2 -37.812958  144.956788  
3 -37.812958  144.956788  
4 -37.812958  144.956788

# Merge Pedestrian Data and Tourist Attraction Data on a Map
# Creating a map
map_melbourne = folium.Map(location=[-37.80841814,144.95906317], zoom_start=14, width=1000, height=600, control_scale=True)
# Pedestrian data
for index, row in data_1_1.iterrows():
    folium.CircleMarker(
        location=[row['latitude'], row['longitude']],
        radius=5,
        color='blue',
        fill=True,
        fill_color='blue',
        fill_opacity=0.6,
        popup=f"Location: {row['sensor_name']}<br>Count: {row['pedestriancount']}",
    ).add_to(map_melbourne)

# Tourist spots data
for index, row in data_3_cleaned.iterrows():
    folium.Marker(
        location=[row['latitude'], row['longitude']],
        popup=f"Name: {row['name']}<br>Type: {row['asset_type']}",
        icon=folium.Icon(color='green', icon='info-sign')
    ).add_to(map_melbourne)

# Adding a legend
legend_html = '''
<div style="position: fixed;
     top: 10px; left: 10px; width: 150px; height: 90px;
     border:2px solid grey; z-index:9999; font-size:14px;
     background-color: white; padding: 10px;">
     <b>Legend</b><br>
     <i style="color:blue;" class="fa fa-circle"></i> Pedestrian Count Sensor Locations<br>
     <i style="color:green;" class="fa fa-info-circle"></i> Tourist Spots</div>
'''
map_melbourne.get_root().html.add_child(folium.Element(legend_html))

# Map displaying
map_melbourne.save('melbourne_map.html')
display(map_melbourne)

# Dataset merge for scatter plot
merged_data_1 = pd.merge(data_1_2, data_4_filtered, on='year_quarter', how='inner')
# Merged_data checking
print(merged_data_1)

# Scatter plot
plt.figure(figsize=(10, 6))
plt.scatter(data_1_2['pedestriancount'], data_4_filtered['visitor_count'], color='blue', alpha=0.5)
plt.title('Scatter Plot of Pedestrian Count vs Tourist Count')
plt.xlabel('Pedestrian Count')
plt.ylabel('Tourist Count')
plt.show()

   year_quarter  pedestriancount  visitor_count
0        2021Q3         19152943        4040786
1        2021Q4         33159337        4361704
2        2022Q1         37898480        4647179
3        2022Q2         46924338        5400025
4        2022Q3         48843359        7219067
5        2022Q4         54960213        8556485
6        2023Q1         57335887        9419538
7        2023Q2         58063867        9829835
8        2023Q3         63283379       10084297
9        2023Q4         70728077       10257111
10       2024Q1         69336334       10365636

# Pearson Correlation Analysis(Pedestrian counting dataset and tourist dataset)
correlation = merged_data_1['pedestriancount'].corr(merged_data_1['visitor_count'])
print(f"Pearson Correlation Coefficient: {correlation}")

Pearson Correlation Coefficient: 0.9384815117290771

# Regression model
# 'fpedestriancount' is independent variable, 'visitor_count' is dependent variable
X = merged_data_1[['pedestriancount']]
y = merged_data_1['visitor_count']
# Dataset split
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# Regression model fit
model = LinearRegression()
model.fit(X_train, y_train)
# Regression model predict
y_pred = model.predict(X_test)
r_squared = model.score(X, y)
mae = mean_absolute_error(y_test, y_pred)
mse = mean_squared_error(y_test, y_pred)
# Regression model results
print(merged_data_1)
print(f"Regression Coefficient: {model.coef_}")
print(f"R-squared: {r_squared}")
print(f"Mean Absolute Error: {mae}")
print(f"Mean Squared Error (MSE): {mse}")

   year_quarter  pedestriancount  visitor_count
0        2021Q3         19152943        4040786
1        2021Q4         33159337        4361704
2        2022Q1         37898480        4647179
3        2022Q2         46924338        5400025
4        2022Q3         48843359        7219067
5        2022Q4         54960213        8556485
6        2023Q1         57335887        9419538
7        2023Q2         58063867        9829835
8        2023Q3         63283379       10084297
9        2023Q4         70728077       10257111
10       2024Q1         69336334       10365636
Regression Coefficient: [0.19797284]
R-squared: 0.8006500040727327
Mean Absolute Error: 1423366.9560733212
Mean Squared Error (MSE): 3168240628361.365

# Regression_coefficient
regression_coefficient = 0.198
# Estimation regional tourists numbers
data_1_1_estimation = data_1_1.copy()
data_1_1_estimation['estimated_tourist_count'] = data_1_1['pedestriancount'] * regression_coefficient
print(data_1_1_estimation.head(10))
print(data_1_1_estimation.shape)

  sensor_name  year_month                    location  pedestriancount  \
0   261Will_T  2022-11-30  -37.81295822, 144.95678789            91518   
1   261Will_T  2022-12-31  -37.81295822, 144.95678789            50214   
2   261Will_T  2023-01-31  -37.81295822, 144.95678789            38818   
3   261Will_T  2023-05-31  -37.81295822, 144.95678789           216910   
4   261Will_T  2023-06-30  -37.81295822, 144.95678789           288651   
5   261Will_T  2023-07-31  -37.81295822, 144.95678789           314142   
6   261Will_T  2023-08-31  -37.81295822, 144.95678789           343645   
7   261Will_T  2023-09-30  -37.81295822, 144.95678789           303683   
8   261Will_T  2023-10-31  -37.81295822, 144.95678789           328492   
9   261Will_T  2023-11-30  -37.81295822, 144.95678789           316824   

    latitude   longitude  estimated_tourist_count  
0 -37.812958  144.956788                18120.564  
1 -37.812958  144.956788                 9942.372  
2 -37.812958  144.956788                 7685.964  
3 -37.812958  144.956788                42948.180  
4 -37.812958  144.956788                57152.898  
5 -37.812958  144.956788                62200.116  
6 -37.812958  144.956788                68041.710  
7 -37.812958  144.956788                60129.234  
8 -37.812958  144.956788                65041.416  
9 -37.812958  144.956788                62731.152  
(2847, 7)

# Calculating the distance between the sensor location and tourist spots location using Haversine
def haversine(lat1, lon1, lat2, lon2):
    R = 6371.0
    phi1 = np.radians(lat1)
    phi2 = np.radians(lat2)
    delta_phi = np.radians(lat2 - lat1)
    delta_lambda = np.radians(lon2 - lon1)

    a = np.sin(delta_phi / 2.0) ** 2 + np.cos(phi1) * np.cos(phi2) * np.sin(delta_lambda / 2.0) ** 2
    c = 2 * np.arctan2(np.sqrt(a), np.sqrt(1 - a))

    return R * c
# Matching tourist spots and the neartest sensor location
def match_closest_pedestrian_data(tourist_data, pedestrian_data):
    matches = []

    for _, tour_row in tourist_data.iterrows():
        min_distance = float('inf')
        closest_pedestrian = None

        for _, ped_row in pedestrian_data.iterrows():
            distance = haversine(tour_row['latitude'], tour_row['longitude'], ped_row['latitude'], ped_row['longitude'])
            if distance < min_distance:
                min_distance = distance
                closest_pedestrian = ped_row
        estimated_tourist_count = closest_pedestrian['pedestriancount'] * regression_coefficient

        matches.append({
            'tourist_location': tour_row['name'],
            'pedestrian_sensor': closest_pedestrian['sensor_name'],
            'pedestrian_count': closest_pedestrian['pedestriancount'],
            'distance_km': min_distance,
            'tour_latitude' : tour_row['latitude'],
            'tour_longitude' : tour_row['longitude'],
            'estimated_tourist_count' : estimated_tourist_count,
            })

    return pd.DataFrame(matches)
# Matching results
matched_data = match_closest_pedestrian_data(data_3_cleaned, data_1_1_estimation)
print(matched_data)
print(f"The number of matched data: {matched_data.shape[0]}")

                tourist_location pedestrian_sensor  pedestrian_count  \
0                       Blowhole           WebBN_T             80218   
1                      Bandstand          Bou231_T            178078   
2               Federation Bells      BirFed1120_T             88316   
3                   Coat of Arms          Swa123_T            257070   
4     Edmund Fitzgibbon Memorial             VAC_T            378528   
..                           ...               ...               ...   
256       Dinny O'Hearn Fountain          Lyg309_T            110294   
257  Westgarth Drinking Fountain          Spr201_T             68561   
258     Pioneer Women's Memorial             VAC_T            378528   
259                Painted Poles           SwaCs_T            152538   
260                        Angel      BirFed1120_T             88316   

     distance_km  tour_latitude  tour_longitude  estimated_tourist_count  
0       0.105429     -37.822018      144.946871                15883.164  
1       0.095419     -37.814055      144.967339                35259.444  
2       0.059180     -37.818674      144.974167                17486.568  
3       0.047369     -37.815158      144.966496                50899.860  
4       0.294161     -37.823612      144.970419                74948.544  
..           ...            ...             ...                      ...  
256     0.054349     -37.797599      144.967306                21838.212  
257     0.554235     -37.805091      144.973417                13575.078  
258     0.767606     -37.825115      144.976076                74948.544  
259     0.005837     -37.815741      144.966836                30202.524  
260     0.081072     -37.818954      144.972959                17486.568  

[261 rows x 7 columns]
The number of matched data: 261

# Preprocessing for time series prediction
data_1_3_estimation = data_1_3.copy()
data_1_3_estimation['year_day'] = pd.to_datetime(data_1_3_estimation['year_day'])
data_1_3_estimation['estimated_tourist_count'] = data_1_3['pedestriancount'] * regression_coefficient
# Drop duplicate sensor 'BirBridge_T'
data_1_3_estimation = data_1_3_estimation[data_1_3_estimation['sensor_name'] != 'BirBridge_T']

# ARIMA model
results = []
sensor_groups = data_1_3_estimation.groupby('sensor_name')
# Time serires model fit and forcast for each areas
for sensor_name, group in sensor_groups:
    if len(group) >= 10:
        print(f"Processing sensor: {sensor_name}")
        ts_data = group.set_index('year_day')['estimated_tourist_count']
        ts_data = ts_data.asfreq('D')
        ts_data = ts_data.ffill()
        ts_data = ts_data.astype(int)

        # Data split
        train_size = int(len(ts_data) * 0.7)
        train, test = ts_data[:train_size], ts_data[train_size:]

        # SARIMA
        model = SARIMAX(train, order=(2, 1, 2), seasonal_order=(1, 1, 1, 12))
        # model = ARIMA(train, order=(3, 1, 2)) Arima model
        model_fit = model.fit()

        # Prediction for test data
        forecast = model_fit.forecast(steps=len(test))

        # RMSE
        rmse = np.sqrt(mean_squared_error(test, forecast))

        # Forcast_increase
        forecast_increase = forecast.iloc[-1] - forecast.iloc[0]

        # AIC, BIC
        aic = model_fit.aic
        bic = model_fit.bic
        print(f"AIC for sensor {sensor_name}: {aic}")
        print(f"BIC for sensor {sensor_name}: {bic}")

        # Results
        results.append({
            'sensor_name': sensor_name,
            'rmse': rmse,
            'forecast_increase': forecast_increase,
        })

        # Results print
        print(f"RMSE for sensor {sensor_name}: {rmse}")
        print(f"Forecast for sensor {sensor_name}:\n{forecast}\n")
        print(f"Forecast Increase for sensor {sensor_name}: {forecast_increase}\n")

        # Visualization
        plt.figure(figsize=(10, 6))
        plt.plot(train.index, train, label='Training Data')
        plt.plot(test.index, test, label='Test Data', color='green')
        plt.plot(test.index, forecast, label='Forecast', color='red')
        plt.title(f'Forecast for Sensor {sensor_name}')
        plt.xlabel('Date')
        plt.ylabel('Estimated Tourist Count')
        plt.legend()
        plt.show()


    else:
        print(f"Skipping sensor {sensor_name} due to insufficient data.")

# result
results_df = pd.DataFrame(results)

Processing sensor: 261Will_T

/usr/local/lib/python3.10/dist-packages/statsmodels/tsa/statespace/sarimax.py:978: UserWarning: Non-invertible starting MA parameters found. Using zeros as starting parameters.
  warn('Non-invertible starting MA parameters found.'
/usr/local/lib/python3.10/dist-packages/statsmodels/base/model.py:607: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals
  warnings.warn("Maximum Likelihood optimization failed to "

AIC for sensor 261Will_T: 7026.453289049711
BIC for sensor 261Will_T: 7055.310738716312
RMSE for sensor 261Will_T: 1511.1323198827379
Forecast for sensor 261Will_T:
2024-02-29    2958.128539
2024-03-01    2277.846615
2024-03-02    1354.034462
2024-03-03    1870.068571
2024-03-04    2368.833645
                 ...     
2024-09-13    3029.898960
2024-09-14    2942.825294
2024-09-15    3001.606386
2024-09-16    2982.194376
2024-09-17    3058.640963
Freq: D, Name: predicted_mean, Length: 202, dtype: float64

Forecast Increase for sensor 261Will_T: 100.51242434579945

Processing sensor: 280Will_T

/usr/local/lib/python3.10/dist-packages/statsmodels/tsa/statespace/sarimax.py:978: UserWarning: Non-invertible starting MA parameters found. Using zeros as starting parameters.
  warn('Non-invertible starting MA parameters found.'
/usr/local/lib/python3.10/dist-packages/statsmodels/base/model.py:607: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals
  warnings.warn("Maximum Likelihood optimization failed to "

AIC for sensor 280Will_T: 5890.875478785476
BIC for sensor 280Will_T: 5919.7329284520765
RMSE for sensor 280Will_T: 698.5605263485924
Forecast for sensor 280Will_T:
2024-02-29    234.624443
2024-03-01    204.916833
2024-03-02    159.874823
2024-03-03    196.698236
2024-03-04    165.566074
                 ...    
2024-09-13   -254.323835
2024-09-14   -257.643967
2024-09-15   -255.914096
2024-09-16   -252.780675
2024-09-17   -251.120044
Freq: D, Name: predicted_mean, Length: 202, dtype: float64

Forecast Increase for sensor 280Will_T: -485.7444864546457

Processing sensor: 474Fl_T

/usr/local/lib/python3.10/dist-packages/statsmodels/base/model.py:607: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals
  warnings.warn("Maximum Likelihood optimization failed to "

AIC for sensor 474Fl_T: 2697.335205115518
BIC for sensor 474Fl_T: 2720.630338297045
RMSE for sensor 474Fl_T: 166.60536942428251
Forecast for sensor 474Fl_T:
2024-06-16    829.618628
2024-06-17    814.165051
2024-06-18    754.447565
2024-06-19    729.296073
2024-06-20    716.673764
                 ...    
2024-09-13    811.476785
2024-09-14    768.027626
2024-09-15    746.754279
2024-09-16    780.489191
2024-09-17    795.153863
Freq: D, Name: predicted_mean, Length: 94, dtype: float64

Forecast Increase for sensor 474Fl_T: -34.46476454352353

Processing sensor: 488Mac_T
AIC for sensor 488Mac_T: 7546.798768227697
BIC for sensor 488Mac_T: 7578.53529009323
RMSE for sensor 488Mac_T: 111.58130698598029
Forecast for sensor 488Mac_T:
2023-11-22    421.290905
2023-11-23    426.814054
2023-11-24    445.853751
2023-11-25    431.060836
2023-11-26    440.632084
                 ...    
2024-09-13    482.019643
2024-09-14    491.343104
2024-09-15    486.866084
2024-09-16    482.180536
2024-09-17    479.362252
Freq: D, Name: predicted_mean, Length: 301, dtype: float64

Forecast Increase for sensor 488Mac_T: 58.07134636677921

# Filtering the top 10 sensors
top_10 = results_df.sort_values(by='rmse').nlargest(10, 'forecast_increase')
print(top_10)

   sensor_name         rmse  forecast_increase
23    Col700_T  1753.170327        3023.296543
63      RMIT_T  1659.274072        2049.594714
60       QVN_T  1170.318378        1466.542436
21    Col620_T   976.372848        1394.464178
76       Swa31  4153.878751        1290.145887
24      ElFi_T  1069.836633        1161.107265
55      MCEC_T  1379.774349        1106.678755
73    Swa123_T  1626.819741        1100.877376
25    Eli250_T  1075.237477         964.489569
75    Swa295_T  1479.962155         922.407188

# 'top_10_results'
data_for_join  = data_1_1_estimation.drop_duplicates(subset=['sensor_name'], keep='first')
# 'left join'
top_10_results = pd.merge(top_10, data_for_join, on='sensor_name', how='left')
print(top_10_results)

  sensor_name         rmse  forecast_increase  year_month  \
0    Col700_T  1753.170327        3023.296543  2021-07-31   
1      RMIT_T  1659.274072        2049.594714  2021-07-31   
2       QVN_T  1170.318378        1466.542436  2021-07-31   
3    Col620_T   976.372848        1394.464178  2021-07-31   
4       Swa31  4153.878751        1290.145887  2021-07-31   
5      ElFi_T  1069.836633        1161.107265  2021-07-31   
6      MCEC_T  1379.774349        1106.678755  2021-07-31   
7    Swa123_T  1626.819741        1100.877376  2021-07-31   
8    Eli250_T  1075.237477         964.489569  2021-07-31   
9    Swa295_T  1479.962155         922.407188  2021-07-31   

                     location  pedestriancount   latitude   longitude  \
0  -37.81982992, 144.95102555            82497 -37.819830  144.951026   
1  -37.80825648, 144.96304859           137066 -37.808256  144.963049   
2  -37.81057846, 144.96444294           327068 -37.810578  144.964443   
3  -37.81887963, 144.95449198           273023 -37.818880  144.954492   
4  -37.81668634, 144.96689733           616339 -37.816686  144.966897   
5  -37.81798049, 144.96503383           559801 -37.817980  144.965034   
6  -37.82401776, 144.95604426           302050 -37.824018  144.956044   
7   -37.81487988, 144.9660878           257070 -37.814880  144.966088   
8   -37.81258467, 144.9625775           303623 -37.812585  144.962578   
9  -37.81101524, 144.96429485           132637 -37.811015  144.964295   

   estimated_tourist_count  
0                16334.406  
1                27139.068  
2                64759.464  
3                54058.554  
4               122035.122  
5               110840.598  
6                59805.900  
7                50899.860  
8                60117.354  
9                26262.126

# 'Find the closest tourist location for each sensor based on 'top_10_results'
matching_results = []

# 'top_10_results'
for i, row_sensor in top_10_results.iterrows():
    min_distance = float('inf')
    closest_location = None
    closest_arttype = None
    closest_lat = None
    closest_lon = None

    # 'tourist spots'
    for j, row_tourist in data_3_cleaned.iterrows():
        distance = haversine(row_sensor['latitude'], row_sensor['longitude'],
                             row_tourist['latitude'], row_tourist['longitude'])

        # updating tourist spots
        if distance < min_distance:
            min_distance = distance
            closest_location = row_tourist['name']
            closest_arttype = row_tourist['asset_type']
            closest_lat = row_tourist['latitude']
            closest_lon = row_tourist['longitude']

    # Save results
    matching_results.append({
        'sensor_name': row_sensor['sensor_name'],
        'sensor_latitude': row_sensor['latitude'],
        'sensor_longitude': row_sensor['longitude'],
        'closest_tourist_location': closest_location,
        'arttype': closest_arttype,
        'tourist_latitude': closest_lat,
        'tourist_longitude': closest_lon,
        'distance_km': min_distance
    })

# Printing result
matching_results_df = pd.DataFrame(matching_results)
print(matching_results_df)

  sensor_name  sensor_latitude  sensor_longitude  \
0    Col700_T       -37.819830        144.951026   
1      RMIT_T       -37.808256        144.963049   
2       QVN_T       -37.810578        144.964443   
3    Col620_T       -37.818880        144.954492   
4       Swa31       -37.816686        144.966897   
5      ElFi_T       -37.817980        144.965034   
6      MCEC_T       -37.824018        144.956044   
7    Swa123_T       -37.814880        144.966088   
8    Eli250_T       -37.812585        144.962578   
9    Swa295_T       -37.811015        144.964295   

          closest_tourist_location   arttype  tourist_latitude  \
0                        Feng Shui       Art        -37.819823   
1           Architectural Fragment       Art        -37.809828   
2                    Painted Poles       Art        -37.810347   
3                            Eagle       Art        -37.820725   
4    Beyond the Ocean of Existence       Art        -37.816424   
5  Captain Matthew Flinders Statue  Monument        -37.816963   
6                            Eagle       Art        -37.820725   
7                     Coat of Arms       Art        -37.815048   
8                 The Public Purse       Art        -37.814098   
9                    Resting Place       Art        -37.810372   

   tourist_longitude  distance_km  
0         144.951105     0.007014  
1         144.964046     0.195443  
2         144.964281     0.029410  
3         144.952391     0.275935  
4         144.967062     0.032577  
5         144.967488     0.243414  
6         144.952391     0.486853  
7         144.966444     0.036469  
8         144.963401     0.183125  
9         144.964077     0.074044

# color list for matched data
colors = ['red', 'blue', 'green', 'purple', 'orange', 'darkred', 'lightred',
          'beige', 'darkblue', 'darkgreen']

# Creating a map
map_top_10 = folium.Map(location=[-37.80841814, 144.95906317], zoom_start=14, width=1000, height=600, control_scale=True)
# top_10_results
for index, row in matching_results_df.iterrows():
    color = colors[index % len(colors)]
    # sensor_location
    folium.CircleMarker(
        location=[row['sensor_latitude'], row['sensor_longitude']],
        radius=5,
        color=color,
        fill=True,
        fill_color=color,
        fill_opacity=0.6,
        popup=f"Sensor: {row['sensor_name']}<br>Count: {row['distance_km']:.2f} km to closest tourist spot",
    ).add_to(map_top_10)

    # tourist spots
    folium.Marker(
        location=[row['tourist_latitude'], row['tourist_longitude']],
        popup=f"Name: {row['closest_tourist_location']}<br>Type: {row['arttype']}",
        icon=folium.Icon(color=color, icon='info-sign')
    ).add_to(map_top_10)


# Adding a legend
legend_html_2 = '''
<div style="position: fixed;
     top: 10px; left: 10px; width: 150px; height: 90px;
     border:2px solid grey; z-index:9999; font-size:14px;
     background-color: white; padding: 10px;">
     <b>Legend</b><br>
     <i style="color:green;" class="fa fa-circle"></i> Nearest Sensor to Tourist Spot<br>
     <i style="color:green;" class="fa fa-info-circle"></i> Top 10 Tourist Spots</div>
'''
map_top_10.get_root().html.add_child(folium.Element(legend_html_2))

# Map save
map_top_10.save('map_top_10.html')
# Display map
display(map_top_10)

# Aggregate by sensor_name and calculate the total.
data_1_1_estimation_sum = data_1_1_estimation.groupby('sensor_name').agg({
    'pedestriancount': 'sum',
    'estimated_tourist_count': 'sum',
    'latitude': 'first',
    'longitude': 'first'
}).reset_index()

print(data_1_1_estimation_sum)

   sensor_name  pedestriancount  estimated_tourist_count   latitude  \
0    261Will_T          4654198               921531.204 -37.812958   
1    280Will_T          2118083               419380.434 -37.812463   
2      474Fl_T          1255299               248549.202 -37.819973   
3     488Mac_T          1973997               390851.406 -37.794324   
4     574Qub_T          1302639               257922.522 -37.803100   
..         ...              ...                      ...        ...   
83       VAC_T         17932918              3550717.764 -37.821299   
84       Vic_T          3242335               641982.330 -37.806069   
85    WatCit_T          3109574               615695.652 -37.815650   
86     WebBN_T          4883437               966920.526 -37.822935   
87    WestWP_T           771742               152804.916 -37.812358   

     longitude  
0   144.956788  
1   144.956902  
2   144.958349  
3   144.929734  
4   144.949081  
..         ...  
83  144.968793  
84  144.956447  
85  144.939707  
86  144.947175  
87  144.971370  

[88 rows x 5 columns]

# Min-Max normalization
scaler = MinMaxScaler()
data_1_1_estimation_sum[['pedestriancount', 'estimated_tourist_count']] = scaler.fit_transform(data_1_1_estimation_sum[['pedestriancount', 'estimated_tourist_count']])

data_1_1_estimation_cluster = data_1_1_estimation_sum.copy()
X = data_1_1_estimation_cluster[['pedestriancount', 'estimated_tourist_count']]

# K-means clustering model
kmeans = KMeans(n_clusters=4, random_state=0)
data_1_1_estimation_cluster['cluster'] = kmeans.fit_predict(X)

# visualization
plt.figure(figsize=(10, 6))
plt.scatter(data_1_1_estimation_cluster['pedestriancount'], data_1_1_estimation_cluster['estimated_tourist_count'],
            c=data_1_1_estimation_cluster['cluster'], cmap='viridis', s=100)
plt.xlabel('Pedestrian Count')
plt.ylabel('Estimated Tourist Count')
plt.title('K-means Clustering of Aggregated Pedestrian and Tourist Counts')
plt.colorbar(label='Cluster')
plt.show()

from sklearn.metrics import silhouette_score
score = silhouette_score(X, data_1_1_estimation_cluster['cluster'])
print(f'Silhouette Score: {score:.2f}')

/usr/local/lib/python3.10/dist-packages/sklearn/cluster/_kmeans.py:1416: FutureWarning: The default value of `n_init` will change from 10 to 'auto' in 1.4. Set the value of `n_init` explicitly to suppress the warning
  super()._check_params_vs_input(X, default_n_init=10)

Silhouette Score: 0.58

# K-means
kmeans = KMeans(n_clusters=4, random_state=0)
data_1_1_estimation_cluster['cluster'] = kmeans.fit_predict(data_1_1_estimation_cluster[['pedestriancount', 'estimated_tourist_count']])

# color list
colors = [ 'purple', 'blue', 'green', 'yellow']

# Creating a map
map_clusters = folium.Map(location=[-37.8136, 144.9631], zoom_start=14, width=1000, height=600, control_scale=True)
for index, row in data_1_1_estimation_cluster.iterrows():
    color = colors[row['cluster']]

    folium.CircleMarker(
        location=[row['latitude'], row['longitude']],
        radius=7,
        color=color,
        fill=True,
        fill_color=color,
        fill_opacity=0.6,
        popup=f"Sensor: {row['sensor_name']}<br>Cluster: {row['cluster']}<br>Pedestrian Count: {row['pedestriancount']}<br>Estimated Tourist Count: {row['estimated_tourist_count']}"
    ).add_to(map_clusters)

# Adding a legend
legend_html_3 = '''
<div style="position: fixed;
     top: 10px; left: 10px; width: 200px; height: 120px;
     border:2px solid grey; z-index:9999; font-size:14px;
     background-color: white; padding: 10px;">
     <b>Legend</b><br>
     <i style="color:yellow;" class="fa fa-circle"></i> Cluster 0: Most crowded<br>
     <i style="color:blue;" class="fa fa-circle"></i> Cluster 1: Second<br>
     <i style="color:purple;" class="fa fa-circle"></i> Cluster 2: Third<br>
     <i style="color:green;" class="fa fa-circle"></i> Cluster 3: Least
</div>
'''

map_clusters.get_root().html.add_child(folium.Element(legend_html_3))


# Displying the map
map_clusters.save('map_clusters.html')
display(map_clusters)

/usr/local/lib/python3.10/dist-packages/sklearn/cluster/_kmeans.py:1416: FutureWarning: The default value of `n_init` will change from 10 to 'auto' in 1.4. Set the value of `n_init` explicitly to suppress the warning
  super()._check_params_vs_input(X, default_n_init=10)

# Mean value of each cluster
cluster_means = data_1_1_estimation_cluster.groupby('cluster')[['estimated_tourist_count']].mean()
# Result
print(cluster_means)

         estimated_tourist_count
cluster                         
0                       0.196986
1                       0.444419
2                       0.057633
3                       0.873733

	asset_type	name	address_point	mel_way_ref	co_ordinates
0	Art	Blowhole	13 Harbour Esplanade, DOCKLANDS	2E_G8	-37.8220182164578, 144.946871022845
1	Art	Bandstand	200 Little Collins Street, MELBOURNE	2F_G3	-37.8140548689972, 144.96733868317
2	Art	Federation Bells	Birrarung Marr, MELBOURNE	2F_ K6	-37.8186738864394, 144.974167165492
3	Art	Coat of Arms	98 Swanston Street, MELBOURNE	2F_F4	-37.8151582937289, 144.966495940551
4	Monument	Edmund Fitzgibbon Memorial	208 St Kilda Road, SOUTHBANK	2F_H9	-37.8236122113264, 144.970418561306

Required modules¶

Data loading¶

Data preprocessing¶

Data visualization¶

Data analysis¶

Conclusion¶

References¶

	sensor_name	year_month	location	pedestriancount
0	261Will_T	2022-11-30	-37.81295822, 144.95678789	91518
1	261Will_T	2022-12-31	-37.81295822, 144.95678789	50214
2	261Will_T	2023-01-31	-37.81295822, 144.95678789	38818
3	261Will_T	2023-05-31	-37.81295822, 144.95678789	216910
4	261Will_T	2023-06-30	-37.81295822, 144.95678789	288651

	year_quarter	pedestriancount
0	2021Q3	19152943
1	2021Q4	33159337
2	2022Q1	37898480
3	2022Q2	46924338
4	2022Q3	48843359

	sensor_name	year_day	location	pedestriancount
0	261Will_T	2022-11-17	-37.81295822, 144.95678789	3993
1	261Will_T	2022-11-18	-37.81295822, 144.95678789	11594
2	261Will_T	2022-11-19	-37.81295822, 144.95678789	3200
3	261Will_T	2022-11-20	-37.81295822, 144.95678789	2855
4	261Will_T	2022-11-21	-37.81295822, 144.95678789	11506

	visitor_count	year_quarter
28	2593347	2021Q1
29	3768841	2021Q2
30	4040786	2021Q3
31	4361704	2021Q4
32	4647179	2022Q1

	0
DateTime	0
Melbourne	0
Regional VIC	0